#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 深入分析device_type_mappings表的数据结构...\n');

try {
  console.log('1. 查看device_type_mappings表的基本信息...');
  
  // 获取表结构
  const tableInfo = db.prepare('PRAGMA table_info(device_type_mappings)').all();
  console.log('表结构:');
  tableInfo.forEach(col => {
    console.log(`  ${col.name}: ${col.type} ${col.notnull ? 'NOT NULL' : ''} ${col.pk ? 'PRIMARY KEY' : ''}`);
  });

  // 获取总记录数
  const totalCount = db.prepare('SELECT COUNT(*) as count FROM device_type_mappings').get();
  console.log(`\n总记录数: ${totalCount.count}`);

  console.log('\n2. 分析记录的分布情况...');
  
  // 按classification_name分组统计
  const byClassification = db.prepare(`
    SELECT 
      classification_name,
      specialty,
      COUNT(*) as mapping_count,
      GROUP_CONCAT(DISTINCT english_name) as english_names,
      GROUP_CONCAT(DISTINCT classification_code) as classification_codes
    FROM device_type_mappings
    GROUP BY classification_name, specialty
    ORDER BY mapping_count DESC, specialty, classification_name
  `).all();

  console.log(`按对象类+专业分组: ${byClassification.length} 组`);
  
  console.log('\n重复映射的对象类 (mapping_count > 1):');
  const duplicates = byClassification.filter(item => item.mapping_count > 1);
  
  if (duplicates.length > 0) {
    duplicates.forEach(item => {
      console.log(`\n  ${item.classification_name} (${item.specialty}) - ${item.mapping_count}条映射:`);
      console.log(`    英文名: ${item.english_names}`);
      console.log(`    分类代码: ${item.classification_codes}`);
    });
  } else {
    console.log('  没有重复映射的对象类');
  }

  console.log('\n3. 分析为什么有98条记录而不是85条...');
  
  // 详细分析每个重复的情况
  console.log('\n详细分析重复映射:');
  
  duplicates.forEach(item => {
    console.log(`\n📋 ${item.classification_name} (${item.specialty}):`);
    
    const details = db.prepare(`
      SELECT *
      FROM device_type_mappings
      WHERE classification_name = ? AND specialty = ?
      ORDER BY classification_code
    `).all(item.classification_name, item.specialty);
    
    details.forEach((detail, index) => {
      console.log(`  记录${index + 1}:`);
      console.log(`    分类代码: ${detail.classification_code}`);
      console.log(`    英文名: ${detail.english_name}`);
      console.log(`    命名表: ${detail.naming_table}`);
      console.log(`    位置表: ${detail.position_table}`);
      console.log(`    基础参数表: ${detail.base_params_table}`);
      console.log(`    技术规格表: ${detail.tech_spec_table || '无'}`);
    });
  });

  console.log('\n4. 分析是否存在不同分类代码对应同一对象类的情况...');
  
  // 检查同一对象类是否有不同的分类代码
  const sameClassDiffCode = db.prepare(`
    SELECT 
      classification_name,
      COUNT(DISTINCT classification_code) as code_count,
      COUNT(DISTINCT specialty) as specialty_count,
      GROUP_CONCAT(DISTINCT classification_code) as all_codes,
      GROUP_CONCAT(DISTINCT specialty) as all_specialties
    FROM device_type_mappings
    GROUP BY classification_name
    HAVING code_count > 1 OR specialty_count > 1
    ORDER BY code_count DESC, specialty_count DESC
  `).all();

  if (sameClassDiffCode.length > 0) {
    console.log(`\n发现 ${sameClassDiffCode.length} 个对象类有多个分类代码或专业:`);
    sameClassDiffCode.forEach(item => {
      console.log(`\n  ${item.classification_name}:`);
      console.log(`    分类代码数: ${item.code_count}`);
      console.log(`    专业数: ${item.specialty_count}`);
      console.log(`    所有代码: ${item.all_codes}`);
      console.log(`    所有专业: ${item.all_specialties}`);
    });
  }

  console.log('\n5. 分析唯一性约束...');
  
  // 检查是否存在完全重复的记录
  const fullDuplicates = db.prepare(`
    SELECT 
      classification_name, specialty, english_name, classification_code,
      COUNT(*) as duplicate_count
    FROM device_type_mappings
    GROUP BY classification_name, specialty, english_name, classification_code
    HAVING duplicate_count > 1
  `).all();

  if (fullDuplicates.length > 0) {
    console.log(`发现 ${fullDuplicates.length} 组完全重复的记录`);
  } else {
    console.log('没有完全重复的记录');
  }

  console.log('\n6. 总结分析结果...');
  
  const uniqueClassifications = byClassification.length; // 85
  const totalMappings = totalCount.count; // 98
  const extraMappings = totalMappings - uniqueClassifications; // 13

  console.log(`\n📊 数据结构总结:`);
  console.log(`- 总映射记录数: ${totalMappings}`);
  console.log(`- 唯一对象类+专业组合: ${uniqueClassifications}`);
  console.log(`- 额外映射记录: ${extraMappings}`);
  console.log(`- 重复映射的对象类: ${duplicates.length}`);
  
  if (duplicates.length > 0) {
    const totalExtraFromDuplicates = duplicates.reduce((sum, item) => sum + (item.mapping_count - 1), 0);
    console.log(`- 重复映射产生的额外记录: ${totalExtraFromDuplicates}`);
  }

  console.log('\n💡 结论:');
  console.log('device_type_mappings表不是按对象类设计的，而是按分类代码(classification_code)设计的');
  console.log('一个对象类可能对应多个不同的分类代码，因此会有多条映射记录');
  console.log('这就是为什么有98条记录而不是85条的原因');

} catch (error) {
  console.error('❌ 分析失败:', error.message);
  console.error(error.stack);
} finally {
  db.close();
}